package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

//		connection between datbase and java code
public class DatabaseConnector {
	static final String DATABASE_URL = "jdbc:mysql://localhost:3306/bitcoinDatabase";
	ResultSet resultSet = null;
	Statement statement = null;
	Connection connection = null;

	private PreparedStatement allUsers = null;
	private PreparedStatement allAccounts = null;
	private PreparedStatement selectAccountBalance = null;
	private PreparedStatement updateBitcoinBalance = null;

	/**
	 * Constructor for the class
	 */
	public DatabaseConnector() {
		try {
			// Creates a connection with the DB based on the url, username and
			// password
			connection = DriverManager.getConnection(DATABASE_URL, "root", "");

			// Creates a request that gets all of the customers in the table
			// customers
			allUsers = connection
					.prepareStatement("SELECT email,name,aes_decrypt(password,'ASH') as password from users");

			// Creates a request that gets all accounts with a specific
			// clientNumber from the table accounts
			allAccounts = connection.prepareStatement("SELECT * FROM accounts");

			// Creates a request for updating the totalBalance with a specific
			// accountNumber & clientNumber from the table accounts
			selectAccountBalance = connection
					.prepareStatement("SELECT bitcoinbalance FROM accounts WHERE userId = ?");

			// Creates a request for udpating the totalBalance with a specific
			// accountNumber & clientNumber from the table accounts
			updateBitcoinBalance = connection
					.prepareStatement("UPDATE accounts SET bitcoinBalance = ? WHERE userId = ?");

		} // end try
		catch (SQLException e) {
			e.printStackTrace();
			System.exit(1);
		} // end catch
	} // end constructor

	/**
	 * Method for getting all the customers
	 * 
	 * @return ArrayList with users from DB
	 */
	public List<User> getUsers() {
		List<User> uj = null;
		ResultSet resultSet = null;
		try {
			/**
			 * Exception handling ?
			 * 
			 * @returns uj
			 */
			resultSet = allUsers.executeQuery();
			uj = new ArrayList<User>();
			while (resultSet.next()) {

				uj.add(new User(resultSet.getString("Email"), resultSet
						.getString("name"), resultSet.getString("password")));

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return uj;
	}

	/**
	 * Gets all Accounts in Arraylist
	 * 
	 * @return ak
	 */
	public List<Account> getAccounts() {
		List<Account> ak = null;
		ResultSet resultSet = null;
		try {

			resultSet = allAccounts.executeQuery();
			ak = new ArrayList<Account>();
			while (resultSet.next()) {
				ak.add(new Account(resultSet.getInt("accountId"), resultSet
						.getString("userId"), resultSet
						.getDouble("bitcoinBalance"), resultSet
						.getString("kontoType")));

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ak;
	}

	public int updateBitcoinBalance(double bitcoinBalance, String userId) {
		{
			int result = 0;
			try {
				updateBitcoinBalance.setDouble(1, bitcoinBalance);
				updateBitcoinBalance.setString(2, userId);
				result = updateBitcoinBalance.executeUpdate();

			} catch (SQLException sqlException) {
				sqlException.printStackTrace();
			}
			return result;
		}

	}

	public String getBalanceList(String userID) {
		ResultSet rs = null;
		String results = null;

		try {
			selectAccountBalance.setString(1, userID);
			rs = selectAccountBalance.executeQuery();
			results = new String();

			while (rs.next()) {
				return rs.getString("bitcoinbalance");
			}
		} catch (SQLException caterpie) {
			caterpie.printStackTrace();
		}
		return results;
	}
}
